﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace QuizzAdminData
{
    /// <summary>
    /// Verzameling van DB methods
    /// </summary>
    /// <author>Kenny Vaneetvelde</author>
    public static class VragenDB
    {
        /// <summary>
        /// Haal alle records uit de tabel
        /// </summary>
        /// <author>Kenny Vaneetvelde</author>
        public static List<Vraag> GetVragen()
        {
            List<Vraag> vragen = new List<Vraag>();

            SqlConnection conn = QuizAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Vragen";

            SqlCommand selectCmd = new SqlCommand(selectStatement, conn);

            try
            {
                conn.Open();
                SqlDataReader reader = selectCmd.ExecuteReader();
                while (reader.Read())
                {
                    Vraag vraag = new Vraag();

                    vraag.Id = (int)reader["id"];
                    vraag.Vraag_tekst = reader["vraag_tekst"].ToString();
                    vraag.Vraag_type = (bool)reader["vraag_type"];

                    vragen.Add(vraag);
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            foreach (Vraag v in vragen)
            {
                v.Antwoorden = AntwoordenDB.GetAntwoordByVraagID(v.Id);
            }

            return vragen;
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void InsertVraagInSpel(int spelID, int vraagID)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Spel_Vragen (spel_id, vraag_id) VALUES (@spel_id, @vraag_id);";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("@spel_id", spelID);
                cmdIns.Parameters.AddWithValue("@vraag_id", vraagID);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void DeleteVraagFromSpel(int spelID, int vraagID)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sqlIns = "DELETE FROM Spel_Vragen WHERE spel_id=@spel_id AND vraag_id=@vraag_id;";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("@spel_id", spelID);
                cmdIns.Parameters.AddWithValue("@vraag_id", vraagID);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void DeleteVraag(int vraagID)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sql =
                "DELETE FROM Vragen " +
                "WHERE id = @id";

            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", vraagID);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <author>Kenny Vaneetvelde</author>
        public static Vraag GetVraagByID(int id)
        {
            Vraag vraag = new Vraag();

            SqlConnection conn = QuizAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Vragen WHERE id=@id";

            SqlCommand selectCmd = new SqlCommand(selectStatement, conn);

            try
            {
                conn.Open();

                selectCmd.Parameters.AddWithValue("id", id);
                SqlDataReader reader = selectCmd.ExecuteReader();

                if (reader.Read())
                {
                    vraag.Id = (int)reader["id"];
                    vraag.Vraag_tekst = reader["vraag_tekst"].ToString();
                    vraag.Vraag_type = (bool)reader["vraag_type"];
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return vraag;
        }

        /// <author>Kenny Vaneetvelde</author>
        public static List<Vraag> GetVragenFromSpelID(int spelID)
        {
            List<Vraag> vragen = new List<Vraag>();

            SqlConnection conn = QuizAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Vragen JOIN " +
                "Spel_Vragen " +
                "ON Vragen.id=Spel_Vragen.vraag_id " +
                "WHERE Spel_Vragen.spel_id=@spel_id";

            try
            {

                conn.Open();

                SqlCommand cmd = new SqlCommand(selectStatement, conn);
                cmd.Parameters.AddWithValue("spel_id", spelID);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Vraag vraag = new Vraag();

                    vraag.Id = (int)reader["id"];
                    vraag.Vraag_tekst = reader["vraag_tekst"].ToString();

                    vragen.Add(vraag);
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return vragen;
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void InsertVraag(string vraagText)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Vragen (vraag_tekst) VALUES (@vraag);";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("@vraag", vraagText);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void UpdateVraag(int id, string vraagText)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sql = "UPDATE Vragen SET vraag_tekst=@vraag_tekst WHERE id=@id";

            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("id", id);
                cmd.Parameters.AddWithValue("vraag_tekst", vraagText);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

    }
}
